using System;
using System.IO;
using System.Data.SqlClient;

namespace Migrator
{
    class TestConnect
    {
        private static String _sqlCommandGetFields = "select t.table_name , c.ordinal_position, c.column_name, c.data_type "+
                                            "from information_schema.tables t " + 
                                            "join information_schema.columns c on t.table_name = c.table_name " +
                                            "where t.table_type = 'BASE TABLE' " +
                                            "order by t.table_name, c.ordinal_position;";

        private static String _sqlCommandGetTables = "select  t.table_name " +
                                                     "from information_schema.tables t " +
                                                     "where t.table_type = 'BASE TABLE';";
        public static void UsageMigrator()
        {
            Console.Write(">> Migrator.exe [-o|-i] [mssql|mysql|oracle|postgres] [output_file]\n");
            Console.Write(">> -o : migrate from [db]\n");
            Console.Write(">> -i : migrate to   [db]\n");
            Console.ReadKey();
        }

        static void Main(string[] args)
        {
            if (args.Length != 3)
            {
                UsageMigrator();
            }

            try
            {
                using (StreamReader sql_file = File.OpenText("../../../../common/sql/" + args[1] + "/reverse.sql"))
                {
                    while (sql_file.ReadLine() != null)
                    {
                        
                    }
                    sql_file.Close();
                    SqlConnect(args[1], "", "");
                }
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
            }
        }

        private static void SqlConnect(String dbms, String dbmsUsername, String dbmsPassword)
        {

            String stringConnect = null;
            if (System.String.CompareOrdinal(dbms, "mssql") == 0)
            {
                stringConnect = "Data Source=BASENOTE\\SQLEXPRESS;Integrated Security=\"True\"";
            }
            if (stringConnect != null)
            {
                var sqlConnection = new SqlConnection(connectionString: stringConnect);

                try
                {
                    sqlConnection.Open();

                    Console.WriteLine("SQL State: {0}", sqlConnection.State);

                    //
                    // Get Tables names
                    //

                    Console.WriteLine("\t\t\t============ TABLES =============");
                    var command = new SqlCommand(_sqlCommandGetTables, sqlConnection);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("TABLE_NAME");
                        Console.WriteLine("====================");
                        while (reader.Read())
                        {
                            Console.Write("{0, -20}", reader.GetString(0));
                            Console.Write("\n");
                        }

                        reader.Close();
                    }

                    //
                    // Get Fields
                    //

                    Console.WriteLine("\t\t\t============ FIELDS =============");
                    command = new SqlCommand(_sqlCommandGetFields, sqlConnection);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("TABLE_NAME            ORDINAL_POSITION  COLUMN_NAME           DATA_TYPE");
                        Console.WriteLine("===================== ================= ===================== ================ ");
                        while (reader.Read())
                        {
                            Console.Write("{0, -22}", reader.GetString(0));
                            Console.Write("{0, -18}", reader.GetInt32(1));
                            Console.Write("{0, -22}", reader.GetString(2));
                            Console.Write("{0, -18}", reader.GetString(3));
                            Console.Write("\n");
                        }
                            
                        reader.Close();
                    }
                    
                    sqlConnection.Close();
                    Console.WriteLine("SQL State: {0}", sqlConnection.State);
                    Console.ReadKey();
                }
                catch (Exception)
                {

                    throw;
                }
                
            }
        }
    }
}

